
    /**  
    * @Title: SingleTableSqlCreateUtil.java
    * @Package com.frames.table.single.operate
    * @Description: 
    * @author likai
    * @date 2018年12月8日
    * @version V1.0  
    */
    
package com.frames.table.single.operate;

import java.util.Map;

import com.utils.transtype.JsonObjectUtil;
import com.utils.transtype.TransType;

/**
    * @ClassName: SingleTableSqlCreateUtil
    * @Description: 单表sql的增删改查自动生成工具
    * @author likai
    * @date 2018年12月8日
    *
    */

public class SingleTableSqlCreateUtil {
	
	/**
	* @Description: 生成插入语句col-value
	* @return String    返回数据:col1,col2...
	*/
	@SuppressWarnings("unchecked")
	public static String insertSqlColVal(Table table) throws Exception {
		StringBuffer insertCol=new StringBuffer();//插入数据的列字段
		StringBuffer insertVal=new StringBuffer();//插入数据的字段值
		StringBuffer insertSqlColVal=new StringBuffer();//插入数据的key-val
		
		Map<String, Object> tablePropVal=JsonObjectUtil.NON_NULL.beanToMap(table);//获取所有有值得属性和值
		Map<String,String> tableCols=TransType.jsonToObject((String) tablePropVal.get("columns"), Map.class);//获取表所有列字段
		tableCols.forEach((key,val)->{
			if (tablePropVal.get(key)!=null&&val!=null) {
				insertCol.append(",").append(val);				
				insertVal.append(",").append("#{table.").append(key).append("}");
			}
		});
		
		insertSqlColVal.append(" (").append(insertCol.toString().replaceFirst(",", ""))
				 .append(") VALUES(").append(insertVal.toString().replaceFirst(",", "")).append(") ");
				
		return insertSqlColVal.toString();
	}
	
	/**
	* @Description: 生成插入冲突更新语句
	* @return String    返回数据:col=val
	*/
	@SuppressWarnings("unchecked")
	public static String insertDuplicateSql(Table table,String col) throws Exception {
		StringBuffer insertDuplicateSql=new StringBuffer();
		
		Map<String, Object> tablePropVal=JsonObjectUtil.NON_NULL.beanToMap(table);//获取所有有值得属性和值
		Map<String,String> tableCols=TransType.jsonToObject((String) tablePropVal.get("columns"), Map.class);//获取表所有列字段
		tableCols.forEach((key,val)->{
			if (val.equals(col)) {
				insertDuplicateSql.append(val).append("=#{table.").append(key).append("}");
			}
		});
				
		return insertDuplicateSql.toString();
	}
	
	/**
	 * @Description: 生成更新的更新值语句
	 * @return String    返回数据:col=val
	 */
	@SuppressWarnings("unchecked")
	public static String updateSqlColVal(Table table,String tableName) throws Exception {
		StringBuffer insertDuplicateSql=new StringBuffer();
		
		Map<String, Object> tablePropVal=JsonObjectUtil.NON_NULL.beanToMap(table);//获取所有有值得属性和值
		Map<String,String> tableCols=TransType.jsonToObject((String) tablePropVal.get("columns"), Map.class);//获取表所有列字段
		tableCols.forEach((key,val)->{
			if (tablePropVal.get(key)!=null&&val!=null&&!val.equals(tablePropVal.get("primaryKey"))) {
				//非主键,且值不为空
				insertDuplicateSql.append(",").append(val).append("=#{").append(tableName).append(".").append(key).append("}");
			}
		});
		
		return insertDuplicateSql.toString().replaceFirst(",", "");
	}
	
	/**
	* @Description: 生成条件语句语句col-value
	* @return String    返回数据:and col1=val and col2>val2...
	*/
	@SuppressWarnings("unchecked")
	public static String conditionSqlColVal(Table table,String tableName,String where) throws Exception {
		StringBuffer conditionSqlColVal=new StringBuffer();//条件数据的key-val	
		SingleTableConditonOpreate[] conditonColOpreate=table.conditonColOpreate;
		
		Map<String, Object> tablePropVal=JsonObjectUtil.NON_NULL.beanToMap(table);//获取所有有值得属性和值
		Map<String,String> tableCols=TransType.jsonToObject((String) tablePropVal.get("columns"), Map.class);//获取表所有列字段
		//生成=的条件
		tableCols.forEach((key,val)->{
			if (tablePropVal.get(key)!=null&&val!=null) {
				//列在表字段存在
				if (conditonColOpreate!=null&&conditonColOpreate.length!=0) {
					String operate="";
					for (int i = 0; i < conditonColOpreate.length; i++) {
						SingleTableConditonOpreate singleTableConditonOpreate=conditonColOpreate[i];
						if (val.equals(singleTableConditonOpreate.getCol().toString())) {
							operate=conditionSqlColMatchVal(singleTableConditonOpreate.getOperate(),tableName,key);
						}
					}
					if (operate.equals("")) {
						//该列未指定运算符，默认等于
						conditionSqlColVal.append(" and ").append(val).append("=#{").append(tableName).append(".").append(key).append("}");			
					}else {
						//该列指定运算符
						conditionSqlColVal.append(" and ").append(val).append(operate);			
					}
				}else {					
					conditionSqlColVal.append(" and ").append(val).append("=#{").append(tableName).append(".").append(key).append("}");				
				}
			}
		});		
		if (conditionSqlColVal.length()==0) {
			return conditionSqlColVal.toString().replaceFirst("and", "");
		}else {
			return new StringBuffer().append(where).append(conditionSqlColVal).toString().replaceFirst("and", "");
		}
	}


	/**
	* @Description: 生成条件语句语句col运算符value
	* @return String    返回数据:and col1=val and col2!=val2...
	*/
	private static String conditionSqlColMatchVal(Operate operate,String tableName,String key) {
		StringBuffer conditionSqlColVal=new StringBuffer();//条件数据的key-val		
		switch (operate) {
		case likeStart:
			conditionSqlColVal.append(" like CONCAT(#{").append(tableName).append(".").append(key).append(")},'%')");
			break;
		case likeEnd:
			conditionSqlColVal.append(" like CONCAT('%',#{").append(tableName).append(".").append(key).append("})");
			break;
		case likeFull:
			conditionSqlColVal.append(" like CONCAT('%',#{").append(tableName).append(".").append(key).append("},'%')");
			break;
		case in:
			conditionSqlColVal.append(" in(#{").append(tableName).append(".").append(key).append("})");
			break;
		default:
			//其他运算
			//=、<、<=、>、>=、!=、null、not null
			conditionSqlColVal.append(operate.getOperate()).append("#{").append(tableName).append(".").append(key).append("}");
			break;
		}
		return conditionSqlColVal.toString();
	}
	
}
